﻿Imports MICROFORMA.BEL
Imports MICROFORMA.DIL
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Data.Common


Public Class RolDA
    Implements RolDI


    Public Function List() As RolBECollection Implements DIL.RolDI.List

        Dim dr As DbDataReader = Nothing

        Dim o_BECollec_Rol_BE As New RolBECollection

        Dim o_Rol_BE As RolBE

        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim query As String = "select * from rol"

        Dim dbCommand As DbCommand = db.GetSqlStringCommand(query)

        dr = db.ExecuteReader(dbCommand)

        While dr.Read()
            o_Rol_BE = New RolBE
            o_Rol_BE.Nombre = dr("Nombre")
            o_Rol_BE.IdRol = dr("IdRol")
            o_Rol_BE.Estado = dr("Estado")
            If dr("Estado") = "0" Then
                o_Rol_BE.EstadoDescripcion = "Activo"
            ElseIf dr("Estado") = "1" Then
                o_Rol_BE.EstadoDescripcion = "Bloqueado"
            End If

            o_BECollec_Rol_BE.Add(o_Rol_BE)
            o_Rol_BE = Nothing
        End While

        Return o_BECollec_Rol_BE

    End Function

    Public Function Add(ByVal _rol_BE As BEL.RolBE) As String Implements DIL.RolDI.Add
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Try
            Dim query As String = "Insert into Rol (IdRol,Nombre,Estado,FechaCreacion)" _
                                    & "Values (" & _rol_BE.IdRol & ",'" & _rol_BE.Nombre _
                                    & "','" & _rol_BE.Estado & "'," & "getdate()" & ")"


            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)


            db.ExecuteNonQuery(o_dbCommand)


            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try
    End Function

    Public Function Edit(ByVal _rol_BE As BEL.RolBE) As String Implements DIL.RolDI.Edit
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Try
            Dim query As String = "Update Rol Set Nombre='" & _rol_BE.Nombre _
                                 & "',FechaModificacion=" & "getDate()" _
                                 & " where IdRol=" & _rol_BE.IdRol


            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)


            db.ExecuteNonQuery(o_dbCommand)


            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try
    End Function

    Public Function Delete(ByVal _rol_BE As BEL.RolBE) As String Implements DIL.RolDI.Delete
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Try
            Dim query As String = "Update Rol Set estado='" & _rol_BE.Estado _
                                 & "',FechaModificacion=" & "getDate()" _
                                 & " where IdRol=" & _rol_BE.IdRol


            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)


            db.ExecuteNonQuery(o_dbCommand)


            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try
    End Function

    Public Function getIdLastRecord() As Int16 Implements DIL.RolDI.getIdLastRecord
        Dim idRol As Int16 = 0

        Dim db As Database = DatabaseFactory.CreateDatabase

        Dim query As String = "select max(idrol) from rol"

        Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim o_Cnx As DbConnection = db.CreateConnection()

        o_Cnx.Open()

        Dim o_Transaction As DbTransaction = o_Cnx.BeginTransaction

        Try
            idRol = Convert.ToInt16(db.ExecuteScalar(o_dbCommand)) + 1


        Catch ex As Exception
            o_Transaction.Rollback()
            Return idRol
        Finally
            o_Cnx.Close()
        End Try

        Return idRol


    End Function

    Public Function GetList() As System.Data.DataTable Implements DIL.RolDI.GetList

        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim query As String = "select * from rol where estado = 0"

        Dim dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim productsDataSet As DataSet = Nothing

        productsDataSet = db.ExecuteDataSet(dbCommand)

        Return productsDataSet.Tables(0)

    End Function

End Class
